

clear all
cap log close

log using "${logfiles}step3_police_workplace.log", replace

* Fleed for years 2006-2011
forvalues i= 2006(1) 2011{
	local current = -5
	while `current' <= 5 {
	
	
	local a = `i' + `current'
	
	use "${data}fleed_kokonais_`a'.dta", clear
	compress
	
	
	if `a' < 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva tol95xht ammattikoodi tyokk kunta ututku toimiala ika tyrtu svatvp 
	}
	
	
	
	if `a' >= 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp
	}

	
	*Industry 
	cap rename toimiala tol95xht
	rename tyotu ttyotul
	rename ammattikoodi amko
	
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 & sykstun!="."

	*Education variables
	g educ=ututku 
	destring educ, replace
	gen level=substr(ututku, 1,1)
	destring level, replace
	
	* Field of study
	gen field=substr(ututku, 2,2)
	destring field, replace
	replace field = 0 if field == .

	* Municipality 
	destring kunta, g(municipality)

	* Earnings
	replace ttyotul = 0 if missing(ttyotul)
	replace tyrtu = 0 if missing(tyrtu)
	replace svatva = 0 if missing(svatva)
	rename svatvp capitalEarnings
	replace capitalEarnings = 0 if missing(capitalEarnings)

	* Sum of labor and enrepreneur earnings 
	gen allEarnings =  ttyotul + tyrtu
	
	gen anyEpreneurEarn = tyrtu > 0 


	/* Merge with baseyear, and keep only those individuals that are in the base sample */
	
  	sort shtun
	drop if mi(shtun)
	drop shnro
	
	fmerge 1:1 shtun using "${dataout}police_DV_workplace_victim_`i'.dta"
	keep if _merge==3
    drop _merge
	
	
	gen time = `current'
	
	save "${dataout}DV_workplace_victim`i'year`a'", replace
	local current = `current' +1 
	}
	
*** Put together data for same baseyear

	local minus1 = `i' - 1
	local minus2 = `i' - 2
	local minus3 = `i' - 3
	local minus4 = `i' - 4
	local minus5 = `i' - 5
	local plus0 = `i' 
	local plus1 = `i' + 1
	local plus2 = `i' + 2
	local plus3 = `i' + 3
	local plus4 = `i' + 4
	local plus5 = `i' + 5
	

	*APPEND FILES*
	
	use "${dataout}DV_workplace_victim`i'year`minus5'.dta", clear
	append using "${dataout}DV_workplace_victim`i'year`minus4'.dta"
	append using "${dataout}DV_workplace_victim`i'year`minus3'.dta"
	append using "${dataout}DV_workplace_victim`i'year`minus2'.dta"
	append using "${dataout}DV_workplace_victim`i'year`minus1'.dta"
	append using "${dataout}DV_workplace_victim`i'year`i'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus1'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus2'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus3'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus4'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus5'.dta"
	

	*Deflate the earnings 
	g year=vuosi
	fmerge m:1 year using  "${dataout}\cpi"
	keep if _merge == 3 
	drop _merge
	
	gen real_earn=ttyotul/cpi
	gen real_inc=svatva/cpi
	gen realEpreEarn = tyrtu/cpi
	gen realAllEarn = allEarnings/cpi
	gen realCapEarn = capitalEarnings/cpi

	
	drop cpi ttyotul svatva 
		
	*INDUSTRY AND OCCUPATION FOR EWCS DATA
	gen nace_2=real(substr(tol95xht, 1,2))

	loc vars "nace"
	foreach var in `vars' {
		g `var'=.
		replace `var'=1 if `var'_2==1 | `var'_2==2 | `var'_2==5
		replace `var'=2 if `var'_2>=10 & `var'_2<=37
		replace `var'=3 if `var'_2==40 | `var'_2==41
		replace `var'=4 if `var'_2==45  
		replace `var'=5 if `var'_2>=50 & `var'_2<=52
		replace `var'=6 if `var'_2==55  

	}

	drop nace_2 
	label var nace "Industry"

	*Occupation codes
	gen occ=real(substr(amko, 1,1))
	display "check how many observations in each occupation"
	
	
	gen baseyr=`i'
	so time baseyr
	order shtun time baseyr
	
	rename vuosi yr	
	sort shtun yr
	compress
	save "${dataout}DV_workplace_victim_`i'.dta", replace
		
}


**** Fleed data for the years 2012-2014 **** 
forvalues i= 2012(1) 2014{
	local current = -5
	while `current' <= 2016-`i' {
	
	display "Baseyear is `i'"
	local a = `i' + `current'
	
	use "${data}fleed_kokonais_`a'.dta", clear
	compress
	
	
	if `a' < 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva tol95xht ammattikoodi tyokk kunta ututku toimiala ika tyrtu svatvp 
	}
	
	
	
	if `a' >= 2005 {
		keep shtun shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp
	}

	
	*Industry 
	cap rename toimiala tol95xht
	rename tyotu ttyotul
	rename ammattikoodi amko
	
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 & sykstun!="."

	*Education variables
	g educ=ututku 
	destring educ, replace
	gen level=substr(ututku, 1,1)
	destring level, replace
	
	
	* Field of study
	gen field=substr(ututku, 2,2)
	destring field, replace
	replace field = 0 if field == .
	

	* Municipality 
	destring kunta, g(municipality)

	* Earnings
	replace ttyotul = 0 if missing(ttyotul)
	replace tyrtu = 0 if missing(tyrtu)
	replace svatva = 0 if missing(svatva)
	rename svatvp capitalEarnings
	replace capitalEarnings = 0 if missing(capitalEarnings)

	* Sum of labor and enrepreneur earnings 
	gen allEarnings =  ttyotul + tyrtu
	
	gen anyEpreneurEarn = tyrtu > 0 


	/* Merge with baseyear, and keep only those individuals that are in the base sample */
	
  	sort shtun
	drop if mi(shtun)
	drop shnro
	
	fmerge 1:1 shtun using "${dataout}police_DV_workplace_victim_`i'.dta"
	keep if _merge==3
    drop _merge
	
	
	gen time = `current'
	
	save "${dataout}DV_workplace_victim`i'year`a'", replace
	local current = `current' +1 
	}
	
		
}


**** FOLK data for 2017-2019 **** 
forvalues i= 2012(1) 2014{
	local current = 2017-`i'
	while `current' <= 5 {
	local a = `i' + `current'

	use "${dataout}FOLK_emp_earnings_`a'.dta", clear
  
	
	rename tyotu ttyotul
	rename tyrtuo tyrtu
	
    
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 



	* Earnings
	replace ttyotul = 0 if missing(ttyotul)
	replace tyrtu = 0 if missing(tyrtu)
	replace svatva = 0 if missing(svatva)
	rename svatvp capitalEarnings
	replace capitalEarnings = 0 if missing(capitalEarnings)

	* Sum of labor and enrepreneur earnings 
	gen allEarnings =  ttyotul + tyrtu



	/* Merge with baseyear, and keep only those individuals that are in the base sample */
	
	rename shnro victim_shnro
  	sort victim_shnro
	drop if mi(victim_shnro)
	
	fmerge 1:1 victim_shnro using "${dataout}police_DV_workplace_victim_`i'.dta"
	keep if _merge==3
    drop _merge
	
	
	gen time = `current'
	
	save "${dataout}DV_workplace_victim`i'year`a'", replace
	local current = `current' +1 
	}
	
		


	
*** Put together data for same baseyear

	local minus1 = `i' - 1
	local minus2 = `i' - 2
	local minus3 = `i' - 3
	local minus4 = `i' - 4
	local minus5 = `i' - 5
	local plus0 = `i' 
	local plus1 = `i' + 1
	local plus2 = `i' + 2
	local plus3 = `i' + 3
	local plus4 = `i' + 4
	local plus5 = `i' + 5

	*APPEND FILES*
	
	use "${dataout}DV_workplace_victim`i'year`minus5'.dta", clear
	append using "${dataout}DV_workplace_victim`i'year`minus4'.dta"
	append using "${dataout}DV_workplace_victim`i'year`minus3'.dta"
	append using "${dataout}DV_workplace_victim`i'year`minus2'.dta"
	append using "${dataout}DV_workplace_victim`i'year`minus1'.dta"
	append using "${dataout}DV_workplace_victim`i'year`i'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus1'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus2'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus3'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus4'.dta"
	append using "${dataout}DV_workplace_victim`i'year`plus5'.dta"


	*Deflate the earnings 
	g year=vuosi
	fmerge m:1 year using  "${dataout}\cpi"
	keep if _merge == 3 
	drop _merge
	
	gen real_earn=ttyotul/cpi
	gen real_inc=svatva/cpi
	gen realEpreEarn = tyrtu/cpi
	gen realAllEarn = allEarnings/cpi
	gen realCapEarn = capitalEarnings/cpi

	
	drop cpi ttyotul svatva 
		
	*INDUSTRY AND OCCUPATION FOR EWCS DATA
	gen nace_2=real(substr(tol95xht, 1,2))

	loc vars "nace"
	foreach var in `vars' {
		g `var'=.
		replace `var'=1 if `var'_2==1 | `var'_2==2 | `var'_2==5
		replace `var'=2 if `var'_2>=10 & `var'_2<=37
		replace `var'=3 if `var'_2==40 | `var'_2==41
		replace `var'=4 if `var'_2==45  
		replace `var'=5 if `var'_2>=50 & `var'_2<=52
		replace `var'=6 if `var'_2==55  

	}

	drop nace_2 
	label var nace "Industry"
	display "check how many observations in each industry"
	

	*Occupation codes
	gen occ=real(substr(amko, 1,1))
	display "check how many observations in each occupation"
	
	
	gen baseyr=`i'
	so time baseyr
	order victim_shnro time baseyr
	
	rename vuosi yr	
	sort victim_shnro yr
	compress
	save "${dataout}DV_workplace_victim_`i'.dta", replace
		
}



*Combine all years
use "W:\workplace\data\DV_workplace_victim_2006.dta", clear
foreach Z of numlist 2007/2014 {
append using "W:\workplace\data\DV_workplace_victim_`Z'.dta"
}

save "W:\workplace\data\DV_workplace_victim_allyears.dta", replace

use "W:\workplace\data\DV_workplace_victim_allyears.dta", clear

rename victim_sykstun_lag sykstun_lag

keep time baseyr syrtun sykstun year suspect_sex plaintiff_sex defendant_shnro victim_shnro ika kunta municipality toimiala ptoim1 ututku amko capitalEarnings tyokk tyrtu tol95xht employed educ level field allEarnings anyEpreneurEarn osalkm tlkm real_earn real_inc realEpreEarn realAllEarn realCapEarn nace nace occ crimecode6 sykstun_lag


// merge victim with defendant data
foreach var in ika kunta municipality toimiala ptoim1 ututku amko capitalEarnings tyokk tyrtu tol95xht employed educ level field allEarnings anyEpreneurEarn osalkm tlkm real_earn real_inc realEpreEarn realAllEarn realCapEarn nace occ crimecode6 sykstun_lag{
	
	rename `var' victim_`var'
	
}

// merge victims with defendant data 
merge 1:1 victim_shnro defendant_shnro time baseyr using "${dataout}DV_workplace_defendant_allyears.dta", keep(3) 
drop _merge

* Save all years defendant and victim long panel data
save "W:\workplace\data\DV_workplace_combined_allyears_2006_2014.dta", replace

cap log close

